﻿using System;
using System.Data;
using System.Configuration;

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for DAL
/// </summary>
public class DataAccessLayer
{
    private string _sql;
    private List<SqlParameter> _parameters;
    private string _connectionString;

    public DataAccessLayer()
    {
        _parameters = new List<SqlParameter>();
        clearQuery();
        ClearParameters();
        if (ConfigurationManager.ConnectionStrings["connString"] == null)
        {
            _connectionString = "Data Source=balder.ucn.dk;Initial Catalog=HOPGroup7DM69;Persist Security Info=True;User ID=HopGroup7DM69;Password=finalproject;";
        }
        else
        {
            _connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
        }
    }

    public DataAccessLayer(string connString)
    {
        _parameters = new List<SqlParameter>();
        clearQuery();
        ClearParameters();
        _connectionString = connString;
    }

    public void setConnString(string Connstring)
    {
        _connectionString = Connstring;
    }

    public void SetQuery(string SQL)
    {
        _sql = SQL;
    }

    public void clearQuery()
    {
        _sql = "";
    }

    public void ClearParameters()
    {
        _parameters.Clear();
    }

    public void AddParameter(string ParameterName, object value, DbType Type)
    {
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = ParameterName;
        parameter.Value = value;
        parameter.DbType = Type;

        _parameters.Add(parameter);
    }

    public void AddParameter(string ParameterName, object Value, SqlDbType Type, int Size)
    {
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = ParameterName;
        parameter.Value = Value;
        parameter.SqlDbType = Type;
        parameter.Size = Size;

        _parameters.Add(parameter);
    }

    public void AddParameter(SqlParameter parameter)
    {
        _parameters.Add(parameter);
    }

    private SqlConnection CreateConnection()
    {
        return new SqlConnection(_connectionString);
    }

    private SqlCommand CreateCommand()
    {
        SqlConnection conn = CreateConnection();
        SqlCommand command = conn.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = _sql;

        foreach (SqlParameter par in _parameters)
        {
            command.Parameters.Add(par);
        }
        return command;
    }

    public int Execute()
    {
        SqlCommand comm = CreateCommand();
        comm.Connection.Open();

        int rows = comm.ExecuteNonQuery();

        comm.Connection.Close();
        ClearParameters();
        clearQuery();
        return rows;
    }

    public void AddParameter(string p, DbType dbType)
    {
        throw new NotImplementedException();
    }

    public object ExecuteScalar()
    {
        SqlCommand command = CreateCommand();
        command.Connection.Open();

        object scalar = command.ExecuteScalar();

        command.Connection.Close();
        ClearParameters();
        clearQuery();
        return scalar;
    }

    public SqlDataReader ExecuteReader()
    {
        SqlCommand command = CreateCommand();
        command.Connection.Open();

        SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
        ClearParameters();
        clearQuery();
        return reader;

    }


}